unit fposclientdb;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, sqlite3, Sqlite3DS, sqlite3db, db, fpstructs, fposUtils, fpg_dialogs,
  fpg_base, fposdialogs;

type

  { TClientDB }

  TClientDB = class(TObject)
  private
    FDataRevision: integer;
    FOnConcludeSale: TSQLEvent;
    FOnResumeShift: TNotifyEvent;
    FOnStaffLogon: TSQLEvent;
    FOnStaffLogout: TSQLEvent;
    FSaleNumber: integer;
    fSalesDB: TSqlite3Dataset;
    fDataDB: TSqlite3Dataset;
    fShiftInfo: TShiftInfo;
    fDataDirectory: TFilename;
    fConfigDir: TFilename;
    fSalesFile: TFilename;
    fLastError: Integer;
    fTerminalID: Integer;
    fDepartments: array of TfpDept;
    function GetDepartment ( deptindex: integer ) : TfpDept;
    function GetDepartmentCount: Integer;
    function GetSaleRef: integer;
    procedure onDBData(Sender: TObject; Columns: Integer; ColumnNames, ColumnValues: String);
  public
    constructor Create(DBDirectory: TFilename);
    destructor Destroy; override;
    procedure GetDepartments;
    function GetTerminalInfo: TSqlite3Dataset;
    function GetDiscounts: TSqlite3Dataset;
    function GetFieldByName(DB: TSQLite; FieldName: string): String;
    function GetPages: TStringList;
    function GetButtons: TSqlite3Dataset;
    function GetConfigRecords(tblname: string): TSqlite3Dataset;
    function UpdateConfig(SQL: string): Boolean;
    function GetAsSQLInsert( tablename, query: string ): string; // returns the results of query as an SQL INSERT
    function GetAsSQLUpdate( tablename, query: string ): string; // returns the results of query as an SQL UPDATE
    function GetTable( tablename, query: string ): TSqlite3Dataset;
    function RegisterSale( SaleList: TObject ): Boolean;
    function SearchForProduct( searchstr: string; ExactMatch: Boolean; SearchInName: Boolean = false ) : integer;
    function StaffLogon: Boolean;
    function StaffLogout: TfpgModalResult;
    function ShiftReport(startSaleNo, endSaleNo: integer): TSqlite3Dataset;
    function SalesReport(startDate, endDate: TJulianDateTime): TSqlite3Dataset;
    function ItemSalesReport(startDate, endDate: extended): TSqlite3Dataset;
    function CheckLastShift: Boolean;

    property ConfigDirectory: string read fConfigDir;
    property TerminalID: integer read fTerminalID write fTerminalID;
    property DataRevision: integer read FDataRevision write FDataRevision;
    property SaleRef: integer read GetSaleRef;
    property OnConcludeSale: TSQLEvent read FOnConcludeSale write FOnConcludeSale;
    property DataDB: TSqlite3Dataset read fDataDB;
    property OnStaffLogon:TSQLEvent read FOnStaffLogon write FOnStaffLogon;
    property OnStaffLogout: TSQLEvent read FOnStaffLogout write FOnStaffLogout;
    property OnResumeShift: TNotifyEvent read FOnResumeShift write FOnResumeShift;
    property ShiftInfo: TShiftInfo read fShiftInfo;
    property DepartmentCount: Integer read GetDepartmentCount;
    property Department[deptindex: integer]: TfpDept read GetDepartment;
  end;


var
  ClientDB: TClientDB;

implementation

uses fpguiTerm, fpg_salelistbox;

{ TClientDB }


procedure TClientDB.onDBData(Sender: TObject; Columns: Integer; ColumnNames,
  ColumnValues: String);
var
  c: PChar;
  i, j: Integer;
  a: TStringList;
begin
{  writeln('Column count: ', ColumnValues);
  if fDataDB.List_Field.Count>0 then
  for i:=0 to fDataDB.List_Field.count-1 do
      begin
        a:=TStringList(fDataDB.List_Field.items[i]);
        write(i,' -> ');
        for j:=0 to a.count-1 do
          write(a.Strings[j],'  ');
        writeln('');
      end;       }
 end;

procedure TClientDB.GetDepartments;
var
  Dept: TfpDept;
  pDept: ^TfpDept;
  x: Integer;
begin
  fDataDB.Close;
  fDataDB.SQL := 'SELECT id, name FROM Departments ORDER BY id;';
  fDataDB.Open;
  SetLength(fDepartments, fDataDB.RecordCount);
  for x := 0 to fDataDB.RecordCount-1 do
    begin
      Getmem(pDept, SizeOf(Dept));
      Dept := pDept^;
      Dept.id := fDataDB.FieldByName('id').AsInteger;
      Dept.name := fDataDB.FieldByName('name').AsString;
      fDepartments[x] := Dept;
      fDataDB.Next;
    end;
end;

function TClientDB.GetSaleRef: integer;
begin
  result := (fTerminalID * 1000000) + FSaleNumber;
end;

function TClientDB.GetDepartmentCount: Integer;
begin
  Result := Length(fDepartments);
end;

function TClientDB.GetDepartment ( deptindex: integer ) : TfpDept;
begin
  if deptindex<0 then exit
  else if deptindex>=DepartmentCount then exit;
  Result := fDepartments[deptindex];
end;

// ClientDB gets created after fposClient but before fposTerm
constructor TClientDB.Create(DBDirectory: TFilename);
begin
  fConfigDir := GetFirePOSConfigDir;
  if not DirectoryExists(fConfigDir)
     then mkdir(fConfigDir);
  if not FileExists(fConfigDir + 'firepos.cfg')
     then CreateConfig(fConfigDir + 'firepos.cfg');
  fDataDB := TSqlite3Dataset.Create(nil);
  with fDataDB do
     begin
       FileName := fConfigDir + 'firepos.cfg';
       TableName := 'Terminal';
     end;
  if DirectoryExists(DBDirectory)
     then fDataDirectory := DBDirectory
     else raise Exception.Create(DBDirectory + ' not found');
end;

destructor TClientDB.Destroy;
begin
  inherited Destroy;
end;

function TClientDB.GetTerminalInfo: TSqlite3Dataset;
begin
  Result := nil;
  {$IFDEF DEBUG}
  writeln('TClientDB.GetTerminalinfo');
  {$ENDIF}
  if fDataDB=nil then Exit;
  fDataDB.Close;
  fDataDB.TableName := 'Terminal';
  fDataDB.SQL := 'SELECT * FROM Terminal';
  fDataDB.Open;
  fTerminalID := fDataDB.FieldByName('TerminalID').AsInteger;
  DataRevision := fDataDB.FieldByName('datarevision').AsInteger;
  if fDataDB.RecordCount>0 then Result := fDataDB;
  {$IFDEF DEBUG}
  if fDataDB.ReturnCode<>0 then WriteLn(fDataDB.ReturnString);
  {$ENDIF}
  if TerminalID>-1 then
     if fSalesDB=nil then
        begin
          fSalesFile := ConfigDirectory + 'terminal-' + fDataDB.FieldByName('TerminalID').AsString + '.dat';
          if not FileExists(fSalesfile)
             then CreateSalesFile(fSalesfile);
          fSalesDB := TSqlite3Dataset.Create(nil);
          fSalesDB.FileName := fSalesFile;
          fSalesDB.TableName := 'Salesummary';
          fSalesDB.SQL := 'SELECT saleid FROM SaleSummary ORDER BY saleid DESC LIMIT 1'; //Get last salenumber
          fSalesDB.Open;
          if fSalesDB.RecordCount=0
             then FSaleNumber := 1
          else FSaleNumber := (fSalesDB.FieldByName('saleid').AsInteger mod 1000000);
          {$IFDEF DEBUG}
          writeln('ClientDB.FSaleNumber=', FSaleNumber);
          {$ENDIF}
          fSalesDB.Close;
          fSalesDB.TableName := 'Shifts';
          fSalesDB.SQL := 'SELECT starttime FROM Shifts ORDER BY starttime DESC LIMIT 1'; //Get last complete shift
          fSalesDB.Open;
          if fSalesDB.RecordCount=0
             then fShiftInfo.StartTime := 0
          else fShiftInfo.StartTime := fSalesDB.FieldByName('starttime').AsFloat;
          {$IFDEF DEBUG}
          writeln('fShiftInfo.StartTime=', fShiftInfo.StartTime);
          {$ENDIF}
        end;

end;

function TClientDB.GetDiscounts: TSqlite3Dataset;
begin
  Result := nil;
  if fDataDB=nil then Exit;
  fDataDB.Close;
  fDataDB.TableName := 'Discounts';
  fDataDB.SQL := 'SELECT * FROM Discounts ORDER by discountid; ';
  fDataDB.Open;
  if fDataDB.RecordCount>0 then Result := fDataDB;
  {$IFDEF LINUX}
  if fDataDB.ReturnCode<>0 then WriteLn(fDataDB.ReturnString);
  {$ENDIF}
end;

function TClientDB.GetFieldByName(DB: TSQLite; FieldName: string): string;
var
  i, j: Integer;
  a: TStringList;
begin
  Result := '';
  if DB=nil then Exit;
  i := 0;
  {$IFDEF LINUX}
  while (i < DB.List_FieldName.Count) and (DB.List_FieldName.Strings[i]<>FieldName) do inc(i);
       writeln('Field index: ', i);
  {$ENDIF}
  if i<DB.List_FieldName.Count then
     if DB.List_Field.Count>0 then
     begin
       a:=TStringList(DB.List_Field.items[0]);
       {$IFDEF LINUX}
       for j := 0 to a.Count-1 do writeln(j, ' -> ', a.Strings[j]);
       {$ENDIF}
       Result := a.Strings[i];
     end;
end;

function TClientDB.GetPages: TStringList;
var
  pgs: TStringList;
begin
  pgs := TStringList.Create;
  Result := pgs;
  if fDataDB=nil then Exit;
  fDataDB.Close;
  fDataDB.TableName := 'Pages';
  fDataDB.SQL := 'SELECT * FROM Pages';
  fDataDB.Open;
  While not fDataDB.EOF do
        begin
          pgs.Add(fDataDB.FieldByName('title').AsString);
          fDataDB.Next;
        end;
end;

function TClientDB.GetButtons: TSqlite3Dataset;
begin
  Result := nil;
  if fDataDB=nil then Exit;
  fDataDB.Close;
  fDataDB.TableName := 'Products';
  fDataDB.SQL := 'SELECT buttons.itemid, products.name AS caption, dept, barcode, saleprice, taxcode, pagenum, row, col, visible, discount FROM Products, Buttons WHERE products.productid=buttons.itemid; ';
  fDataDB.Open;
  if fDataDB.RecordCount>0 then Result := fDataDB;
  {$IFDEF LINUX}
  if fDataDB.ReturnCode<>0 then WriteLn(fDataDB.ReturnString);
  {$ENDIF}
end;

function TClientDB.GetConfigRecords ( tblname: string ) : TSqlite3Dataset;
begin
  Result := nil;
  if fDataDB=nil then Exit;
  fDataDB.Close;
  fDataDB.TableName := tblname;
  fDataDB.SQL := 'SELECT * FROM ' + tblname;
  fDataDB.Open;
  if fDataDB.RecordCount>0 then Result := fDataDB;
  {$IFDEF LINUX}
  if fDataDB.ReturnCode<>0 then WriteLn(fDataDB.ReturnString);
  {$ENDIF}
end;

function TClientDB.UpdateConfig ( SQL: string ) : Boolean;
var
  msg: string;
begin
  SQL := 'BEGIN TRANSACTION;' + SQL ;
  WriteLn('adding update: ', SQL);
  sqlite3_exec(fDataDB.SqliteHandle, PChar(SQL), nil, nil, @msg);
  Result := sqlite3_errcode(fDataDB.SqliteHandle) in [SQLITE_OK, SQLITE_DONE];
  if Result
     then sqlite3_exec(fDataDB.SqliteHandle, PChar('COMMIT;'), nil, nil, @msg)
     else begin
            {$IFDEF LINUX}
            WriteLn(sqlite3_errmsg(fDataDB.SqliteHandle), SQL);
            {$ENDIF}
            sqlite3_exec(fDataDB.SqliteHandle, PChar('ROLLBACK;'), nil, nil, @msg)
          end;
end;

function TClientDB.GetAsSQLInsert(tablename, query: string): string;
var
  fieldstr: String;
  SQLstring: string;
  x: Integer;
  f: String;
begin
  Result := '';
  SQLstring := '';
  fSalesDB.Close;
  fSalesDB.SQL := query;
  fSalesDB.Open;
  writeln('fSalesDB.RecordCount=', fSalesDB.RecordCount);
  if not fSalesDB.IsEmpty then
     begin
       fieldstr := 'INSERT INTO ' + tablename + '(';
       for x := 0 to fSalesDB.FieldCount-1 do
         fieldstr := fieldstr + fSalesDB.Fields[x].FieldName + ',';
       fieldstr[Length(fieldstr)] := ')';  // replace comma
       writeln('fieldstr=', fieldstr);
       fSalesDB.First;
       while not fSalesDB.EOF do
           begin
             SQLstring := SQLstring + fieldstr + ' VALUES (';
             for x := 0 to fSalesDB.FieldCount-1 do
               begin
                  f := fSalesDB.Fields[x].AsString;
                  writeln(f);
                  if Length(f)=0
                     then SQLstring := SQLstring + 'NULL,'
                  else if fSalesDB.Fields[x].DataType in [ftString, ftWideString, ftMemo]
                     then SQLstring := SQLstring + '''' + EscapeQuotes( f ) + '''' + ','
                  else SQLstring := SQLstring + f + ',';
                end;
              SQLstring[Length(SQLstring)] := ')';
              SQLstring := SQLstring + ';' + LineEnding;
              fSalesDB.Next;
           end;
     end;
  writeln('SQLstring=', SQLstring);
  Result := SQLstring;
end;

function TClientDB.GetAsSQLUpdate ( tablename, query: string ) : string;
var
  fieldstr: String;
  SQLstring: string;
  x: Integer;
  f: String;
begin
  Result := '';
  SQLstring := '';
  fSalesDB.Close;
  fSalesDB.SQL := query;
  fSalesDB.Open;
  writeln('fSalesDB.RecordCount=', fSalesDB.RecordCount);
  if not fSalesDB.IsEmpty then
     begin
       fieldstr := 'UPDATE ' + tablename + ' SET ';
       writeln('fieldstr=', fieldstr);
       fSalesDB.First;
       while not fSalesDB.EOF do
           begin
             SQLstring := SQLstring + fieldstr;
             for x := 1 to fSalesDB.FieldCount-1 do
               begin
                  SQLstring := fSalesDB.Fields[x].FieldName + '=';
                  f := fSalesDB.Fields[x].AsString;
                  writeln(f);
                  if Length(f)=0
                     then SQLstring := SQLstring + 'NULL,'
                  else if fSalesDB.Fields[x].DataType in [ftString, ftWideString, ftMemo]
                     then SQLstring := SQLstring + '''' + EscapeQuotes( f ) + '''' + ','
                  else SQLstring := SQLstring + f + ',';
                end;
              SQLstring[Length(SQLstring)] := ' ';
              SQLstring := SQLstring + 'WHERE ' + fSalesDB.Fields[x].FieldName
                                     + '=' + fSalesDB.Fields[x].AsString + ';' + LineEnding;
              fSalesDB.Next;
           end;
     end;
  writeln('SQLstring=', SQLstring);
  Result := SQLstring;
end;

function TClientDB.GetTable ( tablename, query: string ) : TSqlite3Dataset;
begin
  fSalesDB.Close;
  fSalesDB.TableName := tablename;
  fSalesDB.SQL := query;
  fSalesDB.Open;
  Result := fSalesDB;
end;

function TClientDB.RegisterSale ( SaleList: TObject ) : Boolean;
var
  SQL, stockSQL: String;
  x: Integer;
  err: Boolean;
  errmsg: PChar;
  SaleListBox: TfpgSaleListbox;
  RefundOperator: String;
  StocklevelOperator: Char;
begin
  {$IFDEF LINUX}
  writeln('RecordSale');
  {$ENDIF}
  Err := false;
  SQL := '';
  stockSQL := '';
  SaleListBox := TfpgSaleListbox(SaleList);
  RefundOperator := '';
  StocklevelOperator := '-';
  if SaleListBox.isRefund then
    begin
      RefundOperator := '-';
      StocklevelOperator := '+';
    end;
  if fSalesDB <> nil then
     Try
       Inc(FSaleNumber);
       SQL := 'BEGIN TRANSACTION;';
       SQL := SQL + 'INSERT INTO salesummary (saleID, terminalID, saletime, itemcount, total, staffid, refund) '
              + 'VALUES (' + IntToStr(SaleRef)
              + ', ' + IntToStr(fTerminalID)
              + ', ' + DateTimeToJulianDateTimeAsString(Now)
              + ', ' + RefundOperator + IntToStr(SaleListbox.SaleCount)
              + ', ' + FloatToStr( SaleListBox.SaleTotal)
              + ', ' + IntToStr(fShiftInfo.StaffID)
              + ', ' + BoolToStr( TSaleLine(SaleListBox.Items.Objects[0]).isRefund, '1', '0') + ');';
       for x := 0 to SaleListBox.Count-1 do
         begin
         SQL := SQL + 'INSERT INTO saledetails (saleID, itemID, quantity, price) '
                + 'VALUES (' + IntToStr(SaleRef)
                + ', ' + IntToStr( TSaleLine(SaleListBox.Items.Objects[x]).Product.id)
                + ', ' + RefundOperator + IntToStr( TSaleLine(SaleListBox.Items.Objects[x]).Quantity)
                + ', ' + FloatToStr( TSaleLine(SaleListBox.Items.Objects[x]).Product.saleprice
                                         * TSaleLine(SaleListBox.Items.Objects[x]).Quantity) + ');';
         if TSaleLine(SaleListBox.Items.Objects[x]).Product.id > MAX_SERVICEID
            then stockSQL := stockSQL + 'UPDATE Products SET stocklevel=stocklevel' + StocklevelOperator
                              + IntToStr( TSaleLine(SaleListBox.Items.Objects[x]).Quantity)
                              + ' WHERE productid=' + IntToStr( TSaleLine(SaleListBox.Items.Objects[x]).Product.id)
                              + ';';
         end;
       for x := 0 to SaleListBox.Discounts.Count-1 do
         if SaleListBox.Discounts.Items[x].balance>0 then
         begin
           SQL := SQL + 'INSERT INTO saledetails(saleID, itemID, quantity, price) '
                + 'VALUES (' + IntToStr(SaleRef)
                + ', -' + IntToStr( 100+SaleListBox.Discounts.Items[x].discountid )
                + ', 0'
                + ', -' + FloatToStr( SaleListBox.Discounts.Items[x].balance ) + ');';
         end;
       err := sqlite3_exec(fSalesDB.SqliteHandle, PChar(SQL), nil, nil, @errmsg)<>SQLITE_OK;
       if Err then
           begin
             fSalesDB.ExecuteDirect('ROLLBACK');
             ShowMessage('Error when processing sale ' + IntToStr(SaleRef) + LineEnding
                                + errmsg);
             Dec(FSaleNumber);
           end
       else fSalesDB.ExecuteDirect('COMMIT;');

     finally
       Result := not err;
       if Result then
          begin
            if Assigned(FOnConcludeSale)
               then FOnConcludeSale(Self, SQL + stockSQL);  //NOTE: Local database does not track stocklevel at present
          end;
     end;
end;


// if ExactMatch=true then tries to find an exact match in 'barcode' field.
// ExactMatch has no effect on a search of the 'name' field
function TClientDB.SearchForProduct ( searchstr: string; ExactMatch: Boolean; SearchInName: Boolean = false ) : integer;
var
  SQL: String;
  function isNumeric(astring: String): Boolean;
  var
    x: integer;
  begin
    Result := true;
    x := 1;
    try
      while (x<Length(astring)+1) and (astring[x] in ['.','0'..'9']) do
            Inc(x);
    except
      Result := false;
    end;
  end;

begin
  result := -1;
  if isNumeric(searchstr) and (searchstr[1]='2') then // may be price-embedded: must be EAN-13
     begin
       SQL := 'SELECT productid, barcode, name, saleprice, taxcode, dept, discount FROM products '
            + 'WHERE barcode<>"" AND barcode LIKE "' + Copy( searchstr, 1, 7 ) + '%" ORDER BY barcode LIMIT 15;';
     end
  else
    begin
      SQL := 'SELECT productid, barcode, name, saleprice, taxcode, dept, discount FROM products '
             + 'WHERE barcode<>"" AND (barcode LIKE "' + searchstr;
      if ExactMatch then SQL := SQL + '"' else SQL := SQL + '%"';
      if SearchInName then SQL := SQL + ' OR name LIKE "%' + searchstr + '%") ORDER BY name LIMIT 15;'
      else SQL := SQL + ') ORDER BY barcode LIMIT 15;';
    end;
  fDataDB.Close;
  fDataDB.SQL := SQL;
  fDataDB.Open;
  Result := fDataDB.RecordCount;
end;

function TClientDB.StaffLogon: Boolean;
var
  LogonForm: TLogonForm;
  UncleanCloseDlg: TContinueShiftDlg;
  startTime: TJulianDateTime;
  SQL: String;
  lockfilename: String;
  timefile: file of TDateTime;
  locktime : TDateTime;
  interruptTime: String;
  startnewshift: Boolean;
begin
  startnewshift := true;
  //TODO: Request password to continue shift
  if not CheckLastShift then
     begin
       UncleanCloseDlg := TContinueShiftDlg.Create(nil);
       UncleanCloseDlg.OperatorLabel.Text := UncleanCloseDlg.OperatorLabel.Text + ShiftInfo.StaffName;
       UncleanCloseDlg.ShiftStartLabel.Text := UncleanCloseDlg.ShiftStartLabel.Text + FormatJulianDateTime('d mmm yyyy  hh:mm', ShiftInfo.StartTime);
       lockfilename := ConfigDirectory + SHIFT_LOCK;
       interruptTime := 'Not known';
       locktime := Now; // if lockfile cannot be found, terminate shift now
       if FileExists(lockfilename) then
          begin
            AssignFile(timefile,lockfilename);
            Reset(timefile);
            read(timefile, locktime);
            Closefile(timefile);
            interruptTime := FormatDateTime('d mmm yyyy  hh:mm', locktime);
          end;
       UncleanCloseDlg.ClosetimeLabel.Text := UncleanCloseDlg.ClosetimeLabel.Text + interruptTime;
       startnewshift := UncleanCloseDlg.ShowModal=mrCancel;
       UncleanCloseDlg.Free;
       if startnewshift then
          begin
            fSalesDB.Close;
            fSalesDB.SQL := 'SELECT total FROM (SELECT SUM(total) AS total FROM SaleSummary WHERE saleid>=' + IntToStr(fShiftInfo.StartSale)
                         + ')  WHERE total IS NOT NULL;';   // subquery returns a NULL result if total is 0
            fSalesDB.Open;
            if fSalesDB.RecordCount>0
                then fShiftInfo.ReceivedCash := fSalesDB.FieldByName('total').AsFloat
                else fShiftInfo.ReceivedCash := 0.0;
            fSalesDB.ExecuteDirect('UPDATE shifts SET endtime=' + DateTimeToJulianDateTimeAsString(locktime) //TODO: enable next 2 lines
                                + ', endcash=' + FloatToStr( ShiftInfo.ReceivedCash + ShiftInfo.StartCash )
                                + ', endsaleid=' + IntToStr(SaleRef-1)
                                + ' WHERE staffid=' + IntToStr(fShiftInfo.StaffID)
                                + ' AND startsaleid=' + IntToStr(fShiftInfo.StartSale));
          end
       else if Assigned(FOnResumeShift)
         then FOnResumeShift(Self);
     end;

if startnewshift then
    begin
      LogonForm := TLogonForm.Create(nil);
      fDataDB.Close;
      fDataDB.SQL := 'SELECT * FROM staff ORDER BY name';
      fDataDB.Open;
      while not fDataDB.EOF do
            begin
              LogonForm.AddStaffMember(fDataDB.FieldByName('name').AsString);
              fDataDB.Next;
            end;
      Result := LogonForm.ShowModal=mrOK;
      if Result then
         begin
           startTime := DateTimeToJulianDateTime(Now);
           SQL := 'INSERT INTO shifts (terminalid, staffid, starttime, startcash, startsaleid, checksum) '
                                                + 'VALUES (' + IntToStr(fTerminalID) + ', '
                                                + fDataDB.FieldByName('id').AsString + ', '
                                                + FloatToStr(startTime) + ', '
                                                + LogonForm.Cash.Text + ', '
                                                + IntToStr(SaleRef) + ', "'
                                                + Checksum(fDataDB.FieldByName('id').AsString, FloatToStr(startTime), IntToStr(FSaleNumber+1)) + '");';
           fSalesDB.ExecuteDirect(SQL);
           fShiftInfo.StaffID := fDataDB.FieldByName('id').AsInteger;
           fShiftInfo.StaffName := fDataDB.FieldByName('name').AsString;
           fShiftInfo.StaffPriv := fDataDB.FieldByName('privileges').AsInteger;
           fShiftInfo.StaffHash := fDataDB.FieldByName('pass').AsString;
           fShiftInfo.StartSale := SaleRef;
           fShiftInfo.StartTime := startTime;
           fShiftInfo.StartCash := StrToFloat(LogonForm.Cash.Text);
           if Assigned(FOnStaffLogon)
              then FOnStaffLogon(nil, SQL);
         end;
      LogonForm.Free;
    end;
end;

function TClientDB.StaffLogout: TfpgModalResult;
var
  LogoutForm: TLogoutForm;
  SalesSummary: TSqlite3Dataset;
  r: Integer;
  Action: TfpgModalResult;
  lockfilename: String;
  SQL: String;
begin
  LogoutForm := TLogoutForm.Create(nil);
  with LogoutForm do
       begin
         StaffNameLabel.Text := fShiftInfo.StaffName;
         StartTimeLabel.Text := 'Shift start: ' + FormatJulianDateTime('hh:mm', fShiftInfo.StartTime);
         CashStartLabel.Text := 'Cash at start of shift: ' + CurrencyString + FormatFloat(' 0.00', fShiftInfo.StartCash);
         fSalesDB.Close;
         fSalesDB.SQL := 'SELECT total FROM (SELECT SUM(total) AS total FROM SaleSummary WHERE saleid>' + IntToStr(fShiftInfo.StartSale)
                         + ')  WHERE total IS NOT NULL;';   // subquery returns a NULL result if total is 0
         fSalesDB.Open;
         if fSalesDB.RecordCount>0
            then fShiftInfo.ReceivedCash := fSalesDB.FieldByName('total').AsFloat
            else fShiftInfo.ReceivedCash := 0.0;
         CashInLabel.Text := 'Cash received: ' + CurrencyString + FormatFloat(' 0.00', fShiftInfo.ReceivedCash);
         CashEdit.Text := FormatFloat('0.00', fShiftInfo.StartCash + fShiftInfo.ReceivedCash);
         SalesSummary := ShiftReport(fShiftInfo.StartSale, SaleRef);
         if SalesSummary<>nil then
            with ZTotalGrid do
            begin
              RowCount := SalesSummary.RecordCount;
              AddColumn('Dept', 150, taLeftJustify);
              AddColumn('Sales', 50, taCenter);
              AddColumn('Subtotal', 80, taRightJustify);
              ColumnWidth[0] := Width-ColumnWidth[1]-ColumnWidth[2]-4;
              r := 0;
              While not SalesSummary.EOF do
                    begin
                      Cells[0, r] := SalesSummary.FieldByName('deptname').AsString;
                      Cells[1, r] := SalesSummary.FieldByName('sales').AsString;
                      if SalesSummary.FieldByName('subtotal').AsString<>''
                         then Cells[2, r] := FormatFloat('0.00', SalesSummary.FieldByName('subtotal').AsFloat)
                         else Cells[2, r] := '0.00';
                      Inc(r);
                      SalesSummary.Next;
                    end;
            end;
       end;
  Action := LogoutForm.ShowModal;
  if Action=mrOK then
     begin
       SQL := 'UPDATE shifts SET endtime=' + DateTimeToJulianDateTimeAsString(Now)
                            + ', endcash=' + LogoutForm.CashEdit.Text
                            + ', endsaleid=' + IntToStr(SaleRef)
                            + ' WHERE staffid=' + IntToStr(fShiftInfo.StaffID)
                            + ' AND starttime=' + FloatToStr(fShiftInfo.StartTime);
       fSalesDB.ExecuteDirect(SQL);
       if not (fSalesDB.ReturnCode in [0, 101])
          then ShowMessage('Database error at end shift: ' + fSalesDB.ReturnString);
       if Assigned(FOnStaffLogout)
               then FOnStaffLogout(Self, SQL);
       LogoutForm.Free;
       if not StaffLogon then
          begin
            lockfilename := ConfigDirectory + SHIFT_LOCK;
            if FileExists(lockfilename)
              then DeleteFile(lockfilename);
            fposTerm.Close;
          end;
     end
  else LogoutForm.Free;
end;

function TClientDB.ShiftReport ( startSaleNo, endSaleNo: integer
  ) : TSqlite3Dataset;
var
  SQL: String;
begin
  //

  Result := nil;
  fDataDB.Active := false;
  fSalesDB.Close;
  SQL := 'ATTACH "' + fDataDB.FileName + '" AS data;';
  fSalesDB.ExecSQL(SQL);
  SQL := 'SELECT Departments.name AS deptname, SUM(subtotal) AS subtotal, SUM(sales) AS sales FROM Departments'
          + ' LEFT JOIN (SELECT dept, TOTAL(price) AS subtotal, (0-SUM(quantity)) AS sales FROM refunds, data.products'
          + ' WHERE itemID=productID AND saleid>' + IntToStr(startSaleNo)
          + ' AND saleid<=' + IntToStr(endSaleNo) + ' GROUP BY dept'
          + ' UNION SELECT dept, TOTAL(price) AS subtotal, SUM(quantity) AS sales FROM saledetails, data.products '
          + ' WHERE itemID=productID AND saleid>' + IntToStr(startSaleNo)
          + ' AND saleid<=' + IntToStr(endSaleNo) + ' GROUP BY dept ) ON Departments.id=dept GROUP BY deptname ORDER BY Departments.id;';
  {$IFDEF DEBUG}    writeln(SQL); {$ENDIF}
  fSalesDB.SQL := SQL;
  fSalesDB.Open;
  if fSalesDB.ReturnCode=101
     then Result := fSalesDB
     else ShowMessage(fSalesDB.ReturnString);
end;

function TClientDB.SalesReport ( startDate, endDate: TJulianDateTime ) : TSqlite3Dataset;
var
  SQL: String;
  errmsg: string;
begin
  Result := nil;
  fDataDB.Active := false;
  fSalesDB.Close;
  SQL := 'ATTACH "' + fDataDB.FileName + '" AS data;';
  fSalesDB.ExecSQL(SQL);
  SQL := 'SELECT saledetails.saleid AS saleid, saletime, total, data.products.name AS itemname, quantity, price FROM saledetails, salesummary, data.products '
          + 'WHERE saledetails.saleid=salesummary.saleID AND data.products.productID=saledetails.itemID '
          + ' AND saletime>=' + JulianDateTimeToString(startDate)
          + ' AND saletime<' + JulianDateTimeToString(endDate) + ';';
  {$IFDEF DEBUG}    writeln(SQL); {$ENDIF}
  fSalesDB.SQL := SQL;
  fSalesDB.Open;
  if fSalesDB.ReturnCode=101
     then Result := fSalesDB
     else ShowMessage(fSalesDB.ReturnString);
end;

function TClientDB.ItemSalesReport(startDate, endDate: extended
  ): TSqlite3Dataset;
var
  SQL: String;
begin
  Result := nil;
  fSalesDB.Close;
  SQL := 'ATTACH "' + fDataDB.FileName + '" AS data;';
  fSalesDB.ExecSQL(SQL);
  SQL := 'select products.name AS item ,SUM( quantity) as items_sold, TOTAL(ROUND(price*100))/100 As Income FROM Saledetails, salesummary, data.products'
         + ' WHERE salesummary.saletime>=' + IntToStr(Trunc(startDate))
         + ' AND salesummary.saletime<' + IntToStr(Trunc(endDate)+1)
         + ' AND saledetails.saleid=salesummary.saleid AND saledetails.itemid=data.products.productid GROUP BY itemid;';
  fSalesDB.SQL := SQL;
  fSalesDB.Open;
  if fSalesDB.ReturnCode=101
    then Result := fSalesDB
    else ShowMessage(fSalesDB.ReturnString);
end;

function TClientDB.CheckLastShift: Boolean;
var
  SQL: String;
begin
  fSalesDB.Close;
  SQL := 'ATTACH "' + fDataDB.FileName + '" AS data;';
  fSalesDB.ExecSQL(SQL);
  SQL := 'SELECT * FROM ('
         + 'SELECT data.staff.name AS staffname, shifts.staffid AS staffid, data.staff.privileges AS privileges, data.staff.pass AS pass, '
                            + 'starttime, endtime, startcash, startsaleid '
                            + 'FROM Shifts, data.staff WHERE data.staff.id=shifts.staffid ORDER BY starttime DESC LIMIT 1'
         + ') WHERE endtime IS NULL;';
  fSalesDB.SQL := SQL;
  fSalesDB.Open;
  if fSalesDB.RecordCount>0 then
     begin
       fShiftInfo.StaffID := fSalesDB.FieldByName('staffid').AsInteger;
       fShiftInfo.StaffName := fSalesDB.FieldByName('staffname').AsString;
       fShiftInfo.StaffPriv := fSalesDB.FieldByName('privileges').AsInteger;
       fShiftInfo.StaffHash := fSalesDB.FieldByName('pass').AsString;
       fShiftInfo.StartCash := fSalesDB.FieldByName('startcash').AsFloat;
       fShiftInfo.StartSale := fSalesDB.FieldByName('startsaleid').AsInteger;
       fShiftInfo.StartTime := fSalesDB.FieldByName('starttime').AsFloat;
       Result := false;
     end
  else Result := true;
  fSalesDB.Close;
end;

end.
